package com.bmts.gtmc.xls;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Test {
	
	public static void main(String[] args) throws Exception {
		for (int i = 0; i < 6; i++) {
			System.out.println(i);
		}
		//iteratorXLS() ;
		//createXls() ;
	}
	
	public static void iteratorXLS() throws Exception {
		int rownum = 0 ;
		
		Workbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream("E:/Workspaces/MyEclipse 10/DBDataXlsOperation/seed_menu_model.xls"))) ;
		
		Sheet sheet = wb.getSheet("a") ;
		
		for (Iterator<Row> iterator = sheet.rowIterator(); iterator.hasNext();) {
			Row row = (Row) iterator.next();
			
			for (Iterator<Cell> iterator2 = row.cellIterator(); iterator2.hasNext();) {
				Cell cell= (Cell) iterator2.next();
				System.out.print(cell.getStringCellValue()+"\t\t");
			}
			System.out.print("总列数：" + row.getPhysicalNumberOfCells());
			System.out.println("");
			
			
			rownum = rownum + 1 ;
		}
		System.out.println("总行数：" + sheet.getPhysicalNumberOfRows());
		System.out.println("总列数：" +sheet.rowIterator().next().getPhysicalNumberOfCells());
	}
	
	public static void createXls() throws Exception {
		
		Class.forName("oracle.jdbc.driver.OracleDriver") ;
		
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "seed","seed") ;
		
		PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM SEED_MENU_MODEL") ;
		
		ResultSet rs = pstmt.executeQuery() ;
		
		//while(rs.next()) {
			//System.out.println(rs.getObject(1));
		//}
		
		
		Workbook wb = new HSSFWorkbook() ;
		
		Sheet sheet = wb.createSheet("测试POI") ;		//创建工作本
		
		Row row = sheet.createRow(0) ;	//创建一行
		
		Cell c1 = row.createCell(0) ;	//在第一行的第一列中创建列
		c1.setCellValue("编号") ;		//第一列填充数据
		
		Cell c2 = row.createCell(1) ;	//第一行第二列
		c2.setCellValue("公司") ;		//第二列填充数据
		
		Cell c3 = row.createCell(2) ;	//第一行第三列
		c3.setCellValue("所属项目组") ;		//第三列填充数据
		
		Cell c4 = row.createCell(3) ;	//第一行第四列
		c4.setCellValue("姓名") ;		//第四列填充数据
		
		Cell c5 = row.createCell(4) ;	//第一行第五列
		c5.setCellValue("邮箱") ;		//第五列填充数据
		
		Cell c6 = row.createCell(5) ;	//第一行第六列
		c6.setCellValue("联系电话") ;		//第六列填充数据
		
		String[] column = new String[]{"MMId","MMName","MMUrl","MMDesc","Sort","AddDate","Del"}; 
		
		int rownum = 1 ;
		
		while(rs.next()) {
			System.out.println(rownum);
			Row r = sheet.createRow(rownum) ;
		
			for(int i=0; i<column.length; i++) {
				System.out.println(column[i]);
				Cell c = r.createCell(i);
				c.setCellValue(rs.getString(column[i])) ;
			}
			rownum = rownum+1 ;
		}
		
		try {
			FileOutputStream fileOut = new FileOutputStream("workbook.xls") ;
			
			wb.write(fileOut) ;
			
			fileOut.flush() ;
			fileOut.close() ;
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

}
